<?
require("config.php");
require("../bin/php-excel.class.php");

$label = "Order List";

$table="cus_order";

function decode($str){
/*	$output=preg_replace_callback("|&#(\d+);|",
             "convertstr",
             $str);
	return $output;	
*/
//return html_entity_decode ($str);
return $str;
}

function convertstr($matches){
	echo(strtoupper('%'.substr(str_pad(dechex($matches[1]),4,'0',STR_PAD_LEFT),0,2).'%'.substr(str_pad(dechex($matches[1]),4,'0',STR_PAD_LEFT),2,2)));
// return iconv("BIG5","ISO-8859-1",chr(hexdec(substr(str_pad(dechex($matches[1]),4,'0',STR_PAD_LEFT),2,2))).chr(hexdec(substr(str_pad(dechex($matches[1]),4,'0',STR_PAD_LEFT),0,2))));
}

$ftime=strtotime($fy.'-'.$fm.'-'.$fd.' 00:00:00');
$ttime=strtotime($ty.'-'.$tm.'-'.$td.' 23:59:59');
if ($action=="search"){
	if ($no) $nostr=' and no = '.$no;
	else $nostr='';

	if ($firstname) $namestr="%$firstname%";
	else $namestr='';
	if ($lastname) 	$namestr.="$lastname%";
	if ($namestr) 	$namestr=" and member_name like '$namestr'";
	
	if ($member_email) $emailstr=" and member_email like '%$member_email%'";
	else $emailstr='';
	
	if ($hkid) $hkidstr=" and hkid like'%$hkid%'";
	else $hkidstr='';
	

	$Query="SELECT 
	id,
	no,
   pay_method ,
	member_name,
	member_email,
	purchase_date,
	order_status,
	shipping_status,
	delivery_date,
	shipping_carrier,
	shipping_region,
	member_code,
	billing_address,
	subtotal,
	shippingfee
	FROM $table WHERE 
	purchase_date	between $ftime and $ttime
	$nostr
	$namestr
	$emailstr
	$hkidstr
	ORDER BY id DESC";

	$r=myQuery($Query);

}else{

	$Query="SELECT 
	id,
	no,
	pay_method ,
	member_name,
	member_email,
	purchase_date,
	order_status,
	shipping_status,
	delivery_date,
	shipping_carrier,
	shipping_region,
	member_code,
	billing_address,
	subtotal,
	shippingfee
	FROM $table where purchase_date between $ftime and $ttime ORDER BY id DESC";

	$r=myQuery($Query);

}
/*
header("Content-type:application/vnd.ms-excel");  
header("Content-Disposition:filename=orderlist_" . date("Ymd") . ".xls");  



echo "Order No\t";  
echo "Payment Method\t";
echo "Customer Code\t";
echo "Customer Name\t";
echo "Customer Email\t"; 
echo "Order Date\t"; 
echo "Order Status\t";
echo "Delivery Addess\t";
echo "Shipping Region\t";
echo "Shipping Method\t";
echo "Products\t";
echo "Shipping Status\t";
echo "Delivery Date\t";
echo "Subtotal Amount (HKD)\t";
echo "Shipping Fee (HKD)\t";
echo "Order Amount (HKD)\t\n";
for ($i=0;$i<count($r);$i++){

$Query = "SELECT 
t.no,
t.pro_code,
t.pro_color,
t.pro_size,
t.unit_price,
t.qty,
t.id 
FROM cus_order_sub as t WHERE t.no='".$r[$i][1]."'";
$sub=myQuery($Query);

$pro_str='';
for ($j=0;$j<count($sub);$j++) {
	if ($pro_str) $pro_str.=' , ';
	$pro_str.=$sub[$j][1].' * '.$sub[$j][5];
}


if ($r[$i][8]>strtotime('1980-01-01')) $date=date('Y-m-d',$r[$i][8]);
else $date='';

echo str_pad($r[$i][1],5,'0',STR_PAD_LEFT). "\t"; 
echo $r[$i][2] . "\t"; 
echo $r[$i][11]."\t";
echo decode($r[$i][3])."\t";
//echo iconv("ISO-8859-1","UTF8",$r[$i][3]) . "\t"; 
echo $r[$i][4] . "\t"; 
echo date('Y-m-d H:i:s',$r[$i][5]) . "\t"; 
echo $r[$i][6] . "\t"; 
echo str_replace("\t"," ",str_replace("\n"," ",str_replace("\r"," ",$r[$i][12])))."\t";
echo $r[$i][10]."\t";
echo $r[$i][9]."\t";
echo $pro_str."\t";
echo $r[$i][7] . "\t"; 
echo $date . "\t"; 
echo number_format($r[$i][13],2) . "\t"; 
echo number_format($r[$i][14],2) . "\t"; 
echo number_format($r[$i][13]+$r[$i][14],2) . "\t\n"; 
}

echo "\n"; */

$data[] = array("Order No",  
"Payment Method",
"Customer Code",
"Customer Name",
"Customer Email", 
"Order Date", 
"Order Status",
"Delivery Addess",
"Shipping Region",
"Shipping Method",
"Products",
"Shipping Status",
"Delivery Date",
"Subtotal Amount (HKD)",
"Shipping Fee (HKD)",
"Order Amount (HKD)");
for ($i=0;$i<count($r);$i++)
{
	$Query = "SELECT 
	t.no,
	t.pro_code,
	t.pro_color,
	t.pro_size,
	t.unit_price,
	t.qty,
	t.id,
	t.pro_name
	FROM cus_order_sub as t WHERE t.no='".$r[$i][1]."'";
	$sub=myQuery($Query);
	
	$pro_str='';
	for ($j=0;$j<count($sub);$j++) {
		if ($pro_str) $pro_str.=' , ';
		$pro_str.= $sub[$j][1].':'.$sub[$j][7].' * '.$sub[$j][5];
	}
	
	if ($r[$i][8]>strtotime('1980-01-01')) $date=date('Y-m-d',$r[$i][8]);
	else $date='';
	$data[] = array(
		str_pad($r[$i][1],5,'0',STR_PAD_LEFT), 
		$r[$i][2] , 
		$r[$i][11],
		decode($r[$i][3]),
		$r[$i][4], 
		date('Y-m-d H:i:s',$r[$i][5]), 
		$r[$i][6], 
		str_replace("\t"," ",str_replace("\n"," ",str_replace("\r"," ",$r[$i][12]))),
		$r[$i][10],
		$r[$i][9],
		$pro_str,
		$r[$i][7] , 
		$date , 
		number_format($r[$i][13],2) , 
		number_format($r[$i][14],2) , 
		number_format($r[$i][13]+$r[$i][14],2)
	);
}

$xls = new Excel_XML('BIG5', false, 'Orders');
$xls->addArray($data);
$filename = "orderlist_" . date("Ymd") . ".xls";
$xls->generateXML($filename);
?>